
do $$
declare
    l_privs_stmt    text;
    l_privs_type    text;
    l_err_message   text;
    l_len_stmt      int;
    l_len_type      int;
    l_len_message   int;
    l_stmt          text;
begin
    set client_min_messages='ERROR';

    -- 检查 compat_tools 是否存在
    select count(*)::int
      into l_len_type
      from pg_class as c
      join pg_namespace as n on c.relnamespace = n.oid
     where c.relname = 'compat_version'
       and n.nspname = 'compat_tools';

    if l_len_type = 0
    then
        set client_min_messages='NOTICE';
        raise notice ' >>> Compat tool object [compat_tools.compat_version] does not exists !!!';
        return;
    end if;

    -- 创建临时表，记录各个对象的删除情况
    CREATE temporary table if not exists temp_privilege_result
    (
        privs_type      text,
        privs_stmt      text,
        privs_result    text
    ) on commit delete rows;

    -- 刷新 compat 相关表的权限
    if not has_schema_privilege('public', 'compat_tools', 'usage')
    then
        l_stmt := 'grant usage on schema compat_tools to public';
        begin
            execute l_stmt;
            insert into temp_privilege_result values ('Schema', l_stmt, 'Succeed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                insert into temp_privilege_result values ('Schema', l_stmt, 'Error: '||l_err_message);
        end;
    end if;

    for l_stmt in select 'compat_tools.compat_version' union all select 'compat_tools.compat_testing'
    loop
        begin
            if not has_table_privilege('public', l_stmt, 'select')
            then
                l_stmt := 'grant select on table ' || l_stmt || ' to public';
                execute l_stmt;
                insert into temp_privilege_result values ('Table', l_stmt, 'Succeed');
            end if;
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                if l_err_message not like '%does not exist'
                then
                    insert into temp_privilege_result values ('Table', l_stmt, 'Error: '||l_err_message);
                end if;
        end;
    end loop;

    -- 刷新兼容性视图的权限
    for l_stmt in select 'grant select on table ' || object_name || ' to public'
                    from compat_tools.compat_version
                   where compat_type = 'view'
                     and not has_table_privilege('public', object_name, 'select')
    loop
        begin
            execute l_stmt;
            insert into temp_privilege_result values ('View', l_stmt, 'Succeed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                insert into temp_privilege_result values ('View', l_stmt, 'Error: '||l_err_message);
        end;
    end loop;

    -- 刷新兼容性Package schema的权限
    for l_stmt in select 'grant usage on schema ' || cpt.schema_name || ' to public'
                    from (select distinct substr(object_name, 1, instr(object_name, '.') - 1) as schema_name
                            from compat_tools.compat_version
                           where instr(object_name, '.') > 0
                         ) as cpt
                   where cpt.schema_name != 'pg_catalog'
                     and not has_schema_privilege('public', cpt.schema_name, 'usage')
    loop
        begin
            execute l_stmt;
            insert into temp_privilege_result values ('Package', l_stmt, 'Succeed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                insert into temp_privilege_result values ('Package', l_stmt, 'Error: '||l_err_message);
        end;
    end loop;

    -- 刷新兼容性Package函数的权限
    for l_stmt in select 'grant execute on function '
                      || cpt.schema_name || '.' || proc.proname
                      || '(' || pg_get_function_identity_arguments(proc.oid) || ')'
                      || ' to public'
                    from (select distinct substr(object_name, 1, instr(object_name, '.') - 1) as schema_name
                            from compat_tools.compat_version
                           where instr(object_name, '.') > 0
                         ) as cpt
                    join pg_catalog.pg_namespace as nsp on cpt.schema_name = nsp.nspname
                    join pg_catalog.pg_proc as proc on nsp.oid = proc.pronamespace
                   where cpt.schema_name != 'pg_catalog'
                     and (not has_function_privilege('public', proc.oid, 'execute'))
    loop
        begin
            execute l_stmt;
            insert into temp_privilege_result values ('Function', l_stmt, 'Succeed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                insert into temp_privilege_result values ('Function', l_stmt, 'Error: '||l_err_message);
        end;
    end loop;

    -- 显示权限刷新结果
    if current_setting('application_name') != 'runMe'
    then
        select max(length(privs_type)) + 1
             , max(length(privs_stmt)) + 1
             , max(length(privs_result)) + 1
          into l_len_type, l_len_stmt, l_len_message
          from temp_privilege_result;

        set client_min_messages='NOTICE';
        if l_len_stmt is null
        then
            raise notice '-- <<< ALL PRIVILEGES ARE EXPECTED >>>';
        else
            raise notice '%', '|-'||rpad('-', l_len_type, '-')||'---'||rpad('-', l_len_stmt, '-')||'---'||rpad('-', l_len_message, '-')||'-|';
            raise notice '%', '| '||rpad('TYPE', l_len_type)||' | '||rpad('NAME', l_len_stmt)||' | '||rpad('RESULT', l_len_message)||' |';
            raise notice '%', '|-'||rpad('-', l_len_type, '-')||'-+-'||rpad('-', l_len_stmt, '-')||'-+-'||rpad('-', l_len_message, '-')||'-|';
            for l_privs_type, l_privs_stmt, l_err_message in select privs_type
                                                                    , privs_stmt
                                                                    , privs_result
                                                                 from temp_privilege_result
                                                                order by privs_result, privs_type, privs_stmt
            loop
                raise notice '%', '| '||rpad(l_privs_type, l_len_type)||' | '||rpad(l_privs_stmt, l_len_stmt)||' | '||rpad(l_err_message, l_len_message)||' |';
            end loop;
            raise notice '%', '|-'||rpad('-', l_len_type, '-')||'---'||rpad('-', l_len_stmt, '-')||'---'||rpad('-', l_len_message, '-')||'-|';
        end if;
    end if;
end;
$$ language plpgsql;
